package App;

use strict;
use warnings;
use lib "./libs";

use Utils ();
use Const ();

use Exporter qw(import);

use Carp;
use DBI;
use Utils;
use Utils qw(dd);
use Log;

Log::setLevel($Log::level_error | $Log::level_warn | $Log::level_primary | $Log::level_info | $Log::level_debug);

my %dbhs;

sub getDBHs
{
    return %dbhs;
}

sub dbIndex
{
    my $config = shift;
    my $type = $config->{type};

    my $index;
    if ($type eq "Oracle") {
        $index = "$type - $config->{host} - $config->{port} - $config->{sid} - $config->{user}";
    } elsif ($type eq "PostgreSQL") {
        $index = "$type - $config->{host} - $config->{port} - $config->{database} - $config->{user}";
    } else {
        croak "Unsupported database type: $type";
    }

    return $index;
}

sub initDBConfig
{
    my $config = shift;
    
    my $index = dbIndex($config);
    unless (exists($dbhs{$index})) {
        $dbhs{$index}{config} = $config;
    }
}

sub closeDB
{
    my $config = shift;
    my $index = dbIndex($config);
    if (exists($dbhs{$index})) {
        $dbhs{$index}{dbh}->disconnect;
        delete $dbhs{$index};
    }
}

sub getDB
{
    my $config = shift;
    my $index = dbIndex($config);
    unless (exists($dbhs{$index})) {
        $dbhs{$index}{config} = $config;
    }
    unless (exists($dbhs{$index}{dbh})) {
        $dbhs{$index}{dbh} = connectDB($config);
    }
    return $dbhs{$index};
}

sub connectDB
{
    my $config = shift;
    my $type = $config->{type};
    my $host = $config->{host};
    my $port = $config->{port};
    my $user = $config->{user};
    my $pass = $config->{pass};

    Log::info("Connection to database, type: $type, host: $host, port: $port, user: $user, pass: $pass");

    my $dbh;

    if ($type eq "Oracle") {
        my $sid = $config->{sid};

        Log::info("Connection command: [ sqlplus $user/'$pass'\@$host:$port/$sid ]");
        $dbh = DBI->connect("dbi:Oracle:$host:$port/$sid", $user, $pass, {
            RaiseError => 1,
            AutoCommit => 1,
            ora_charset => 'AL32UTF8',
            ora_nls_lang => 'AMERICAN_AMERICA.AL32UTF8'
        });
        croak "Failed to connect to Oracle database: $DBI::errstr" unless $dbh;
    } elsif ($type eq "PostgreSQL") {
        Log::info("Connection command: [ gsql -h $host -p $port -U $user -d $config->{database} -W'$config->{pass}' -r ] ");

        my $driver   = "Pg";
        my $database = $config->{database};
        $dbh = DBI->connect("dbi:Pg:dbname=$database;host=$host;port=$port", $user, $pass, {
            RaiseError => 1,
            AutoCommit => 1
        });
        croak "Failed to connect to PostgreSQL database: $DBI::errstr" unless $dbh;
    }

    return $dbh;
}

sub getCount
{
    my $db = shift;
    my $table = shift;
    my $schema = shift;

    unless (defined($db)) {
        croak "Invalid db";
    }

    unless ($db->{config}->{type} eq "Oracle" || $db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    my $count;

    $db->{dbh}->{Profile} = 2;
    my $sql = "SELECT COUNT(1) FROM $schema.${table}";
    Log::info("  Execute SQL: $sql");
    $count = $db->{dbh}->selectrow_array($sql);
    Log::info("  Execute time: $db->{dbh}->{Profile}{Data}{$sql}[1].");
    $db->{dbh}->{Profile}{Data} = undef;
    # Log::info("Exection information:");
    $db->{dbh}->{Profile} = undef;

    return $count;
}

sub getSum
{
    my $db = shift;
    my $table = shift;
    my $column = shift;
    my $schema = shift;

    unless (defined($db)) {
        croak "Invalid db";
    }

    unless ($db->{config}->{type} eq "Oracle" || $db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    my $sum;

    $db->{dbh}->{Profile} = 2;
    my $sql = "SELECT SUM(${column}) FROM $schema.${table}";
    Log::info("    Execute SQL: $sql");
    $sum = $db->{dbh}->selectrow_array($sql);
    Log::info("    Execute time: $db->{dbh}->{Profile}{Data}{$sql}[1].");
    $db->{dbh}->{Profile}{Data} = undef;
    # Log::info("Exection information:");
    $db->{dbh}->{Profile} = undef;

    unless (defined $sum) {
        $sum = 0;
    }
    return $sum;
}

sub getRow
{
    my ($db, $table, @conditions, $limit) = @_;

    unless (defined($db)) {
        croak "Invalid db";
    }
    unless ($db->{config}->{type} eq "Oracle" || $db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    my $sql = "SELECT * FROM ${table} WHERE 1=1 ";
    
    if (scalar(@conditions) > 0) {
        $sql.= " AND " . join(" AND ", @conditions);
    }
    if (defined($limit)) {
        if ($db->{config}->{type} eq "Oracle") {
            $sql .= " AND ROWNUM <= ${limit}";
        } elsif ($db->{config}->{type} eq "PostgreSQL") {
            $sql .= " LIMIT ${limit}";
        }
    }
    Log::info("  Execute SQL: $sql");
    my $row = $db->{dbh}->selectrow_hashref($sql);
    return $row;
}

sub getBaseRow
{
    my ($db, $table, $schema) = @_;
    unless (defined($db)) {
        croak "Invalid db";
    }

    my $sql;
    if ($db->{config}->{type} eq "Oracle") {
        $sql = "SELECT * FROM (SELECT * FROM $schema.${table} ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1";
    } elsif ($db->{config}->{type} eq "PostgreSQL") {
        $sql = "SELECT * FROM $schema.${table} ORDER BY RANDOM() LIMIT 1";
    } else {
        croak "Unsupported database type: $db->{config}->{type}";
    }
    Log::info("  Execute SQL: $sql");
    my $row = $db->{dbh}->selectrow_hashref($sql);
    return $row;
}

sub selectRow {
    my ($db, $sql) = @_;
    unless (defined($db)) {
        croak "Invalid db";
    }
    Log::info("  Execute SQL: $sql");
    my $row = $db->{dbh}->selectrow_hashref($sql);
    return $row;
}

sub selectAll {
    my ($db, $sql) = @_;
    unless (defined($db)) {
        croak "Invalid db";
    }
    Log::info("  Execute SQL: $sql");
    my $rows = $db->{dbh}->selectall_arrayref($sql);
    return $rows;
}

sub getTableColumns
{
    my ($db, $table, $schema) = @_;
    unless (defined($db)) {
        croak "Invalid db";
    }
    unless ($db->{config}->{type} eq "Oracle" || $db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }
    my $sql;
    if ($db->{config}->{type} eq "Oracle") {
        croak "Unsupported database type: Oracle";
    } else {
        $sql = "SELECT * FROM information_schema.columns WHERE table_schema = LOWER('$schema') and table_name = LOWER('$table') ORDER BY ordinal_position";
    }
    Log::info("  Execute SQL: $sql");
    my $sth = $db->{dbh}->prepare($sql);
    $sth->execute();
    my @columns;
    while (my $row = $sth->fetchrow_hashref) {
        push @columns, $row;
    }
    return @columns;
}

sub exec {
    my ($db, $sql, $schema) = @_;

    unless (defined($db)) {
        croak "Invalid db";
    }
    unless ($db->{config}->{type} eq "Oracle" || $db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    Log::info("  Execute SQL: $sql");
    my $sth = $db->{dbh}->prepare($sql);
    unless ($sth) {
        croak "Failed to prepare SQL: $DBI::errstr";
    }

    $sth->execute();
    unless ($sth) {
        croak "Failed to execute SQL: $DBI::errstr";
    }

    my $rows = $sth->fetchall_arrayref({});
    unless ($rows) {
        croak "Failed to fetch results: $DBI::errstr";
    }
    $sth->finish();

    return $rows;
}

sub createTable {
    my ($db, $sql) = @_;

    unless (defined($db)) {
        croak "Invalid db";
    }
    unless ($db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    Log::info("  Execute SQL: $sql");

    # 设置 AutoCommit 为 1，确保每个语句独立提交
    my $old_auto_commit = $db->{dbh}->{AutoCommit};
    $db->{dbh}->{AutoCommit} = 1;

    my $sth = $db->{dbh}->prepare($sql);
    unless ($sth) {
        croak "Failed to prepare SQL: $DBI::errstr";
    }

    my $result = $sth->execute();
    unless ($result) {
        croak "Failed to execute SQL: $DBI::errstr";
    }

    $sth->finish();

    # 恢复原来的 AutoCommit 设置
    $db->{dbh}->{AutoCommit} = $old_auto_commit;

    return 1;  # 返回成功标志
}

sub getRowLimit
{
    my ($db, $row_limit) = @_;

    # 设置默认值
    $row_limit //= 1000;

    # 验证 $row_limit 是否为正整数
    unless ($row_limit =~ /^\d+$/ && $row_limit > 0) {
        croak "Invalid row limit: $row_limit";
    }
    my $limit;
    if ($db->{config}->{type} eq "Oracle") {
        $limit = " ) WHERE ROWNUM <= $row_limit ";
    } elsif ($db->{config}->{type} eq "PostgreSQL") {
        $limit = " LIMIT $row_limit ";
    } else {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    return $limit;
}

1;

__END__
